package com.lee.dutylower.utils;

import android.content.Context;
import android.os.Environment;
import android.util.Log;

import com.lee.dutylower.db.greenEntity.PostRecord;
import com.lee.dutylower.db.greendao.PostRecordDao;
import com.lee.dutylower.domain.CellTemplate;
import java.io.File;
import java.io.InputStream;
import jxl.Cell;
import jxl.Sheet;
import jxl.Workbook;
import jxl.format.Border;
import jxl.format.BorderLineStyle;
import jxl.format.Colour;
import jxl.write.Label;
import jxl.write.WritableCellFormat;
import jxl.write.WritableFont;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;

public class ExcelUtil {
    //内存地址
    public static String root = Environment.getExternalStorageDirectory()
            .getPath();

    private static String[] weeks = new String[]{"周一","周二","周三","周四","周五","周六","周日"};
    /**
     * 根据模板，生成excel文件
     *
     * @return
     */
    public static void UpdateExcelByTemplate(Context context, PostRecordDao postRecordDao, int week) throws Exception {
        WritableFont font = new WritableFont(WritableFont.TAHOMA, 16,
                WritableFont.NO_BOLD);// 定义字体
        WritableCellFormat format = new WritableCellFormat(font);
        format.setAlignment(jxl.format.Alignment.CENTRE);// 左对齐
        format.setVerticalAlignment(jxl.format.VerticalAlignment.CENTRE);// 上下居中
        format.setBorder(Border.ALL, BorderLineStyle.THIN, Colour.BLACK);// 黑色边框
        format.setWrap(true);//自动换行

        InputStream is = context.getAssets().open("temp.xls");
        // 读取模板
        //Workbook mWorkbook = Workbook.getWorkbook(new File("/storage/sdcard1/Models/repTemplate/"+record.getExcId()+".xls"));
        Workbook mWorkbook = Workbook.getWorkbook(is);
        Sheet mSheet = mWorkbook.getSheet(0);
        int sRow = mSheet.getRows();
        int sColumns = mSheet.getColumns();
        Log.i("W", "Total sRow: " + sRow + ", Total sColumns: " + sColumns);
        File fileDir = new File(Environment.getExternalStorageDirectory().getAbsolutePath()+"/PostExcel");
        if (!fileDir.exists()){
            fileDir.mkdir();
        }
        // 根据模板创建新excel，填充模板内容
        WritableWorkbook mWritableWorkbook = Workbook.createWorkbook(new File(Environment.getExternalStorageDirectory().getAbsolutePath()+"/PostExcel/duty_lower_week"+week+".xls"), mWorkbook);
        WritableSheet mWritableSheet = mWritableWorkbook.getSheet(0);
        int dRow = mSheet.getRows();
        int dColumns = mSheet.getColumns();
        Log.i("EXCEL", "Total dRow: " + dRow + ", Total dColumns: " + dColumns);
        for (int i = 0; i < dRow; i++) {
            // 获取单元格行
            int cols = mWritableSheet.getRow(i).length;
            for (int j = 0; j < cols; j++) {
                // 获取单元格列
                Cell temp = mWritableSheet.getCell(j, i);
                String content = temp.getContents();
                if (content.contains("{")) {
                    try {
                        // 如果此区域为json内容，则为模板配置，需要填充
                        CellTemplate cellTemplate = (CellTemplate) JsonUtils.fromJson(content, CellTemplate.class);
                        String dataType = cellTemplate.getType();
                        if (dataType.equals("data")) {
                            long time = cellTemplate.getTimeid();
                            long sentry = cellTemplate.getWeek();
                            PostRecord pr = postRecordDao.queryBuilder().where(PostRecordDao.Properties.TimesID.eq(time)
                                    , PostRecordDao.Properties.SentryID.eq(sentry)
                                    , PostRecordDao.Properties.Dayofweek.eq(week)).build().unique();
                            String name = "";
                            if (pr != null)
                                name = pr.getSoldier().getSName();
                            ((Label) temp).setString(name);
                            ((Label) temp).setCellFormat(format);
                        } else if (dataType.equals("title")) {
                            String s = weeks[week-1];
                            ((Label) temp).setString("监护三中队勤务派位表    "+s);
                            WritableFont titlefont = new WritableFont(WritableFont.COURIER, 20,
                                    WritableFont.BOLD);// 定义字体
                            WritableCellFormat titleformat = new WritableCellFormat(titlefont);
                            titleformat.setAlignment(jxl.format.Alignment.CENTRE);// 左对齐
                            titleformat.setVerticalAlignment(jxl.format.VerticalAlignment.CENTRE);// 上下居中
                            titleformat.setBorder(Border.ALL, BorderLineStyle.THIN, Colour.BLACK);// 黑色边框
                            titleformat.setWrap(true);//自动换行
                            ((Label) temp).setCellFormat(titleformat);
                        }
                    } catch (Exception e) {
                        // 非模板配置，不需要填充
                        System.out.println("生成模版异常" + e.toString());
                    }
                }
            }
        }

        // 关闭资源流
        mWritableWorkbook.write();
        mWritableWorkbook.close();
        mWorkbook.close();
    }

    private static String getwholestatue(String s) {
        String re = null;
        if (s.equals("1")) {
            re = "合格";
        }
        if (s.equals("2")) {
            re = "未合格";
        }
        if (s.equals("3")) {
            re = "强制合格";
        }
        return re;
    }
}
